package com.ws.shop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Date;
import java.util.Map;
import java.util.Set;

import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import com.ws.shop.bean.Page;
import com.ws.shop.dao.TicketDao;
import com.ws.shop.entity.Ticket;
import com.ws.shop.util.JdbcUtil;

@Repository(value="ticketDao")
public class TicketDaoImpl extends BaseDaoImpl implements TicketDao {

	private static Logger log = Logger.getLogger(TicketDaoImpl.class);
			
	final String findTicketListPage_count = "select count(1) from jvf_ticket";
	final String findTicketListPage_list = "select id,cardsn,cardpwd,starttime,endtime,beat,pointb,state,addtime from jvf_ticket";
	final String findTicketListPage_order = " order by id desc";
	public Page findTicketListPage(int pageno,int pagesize) {
		return findPage(pageno,pagesize,findTicketListPage_count
				,new StringBuilder(findTicketListPage_list).append(findTicketListPage_order));
	}
	
    //点券充值
	public int chargeCard(final String cardsn,final String cardpwd,final long uid,final String name){
		return (Integer)this.execute(
				new ConnectionCallback(){
					@SuppressWarnings("unchecked")
					public Object doInConnection(Connection connect)
					throws SQLException, DataAccessException {
						Statement stmt = null;
						ResultSet rs = null;
						try{
							connect.setAutoCommit(false);
							String s = "SELECT  state,startime,endtime,beat, pointb,id FROM jvf_ticket  WHERE cardsn = '"+cardsn+"' AND cardpwd = '" + cardpwd + "' for update" ;
							stmt = connect.createStatement();
							rs = stmt.executeQuery(s);
							if(rs.next()){ 
								int state = rs.getInt(1);
								Date starttime = rs.getTimestamp(2);
								Date endtime = rs.getTimestamp(3);
								int beat = rs.getInt(4);
								int pointb = rs.getInt(5);
								long id = rs.getLong(6);
								if(state!=0){
									connect.rollback();
									return 2; //已经被使用
								}else{
									Date now = new Date();
									if (starttime .compareTo(now)< 0  && endtime.compareTo(now) > 0){
										s = "update jvf_user set beat = beat + "+beat+" ,pointb = pointb + ? where id = " + uid;
										stmt = connect.createStatement();
										stmt.addBatch(s);
										String payment = "点券";
										String remark = "点券用户充值点券 beat " + beat+ " 拍点："+pointb;
										s = "INSERT INTO jvf_transaction(uid,NAME,payment,ADDTIME,remark) VALUES("+uid+",'"+name+"','"+payment+"',now(),'"+remark+"');";
										stmt.addBatch(s);
										s = "update jvf_ticket set state = 1 where id = "+ id;
										stmt.addBatch(s);
										stmt.executeBatch();
										connect.commit();
										return 0;
									}else{
										connect.rollback();
										return 3; //已经过期
									}
								}
							}else{
								connect.rollback();
								return 1;//不存在该点券
							}
						}catch (Exception e) {
							e.printStackTrace();
							connect.rollback();
							log.error(e.getMessage(), e);
							return  -1;
						} finally {
							JdbcUtil.close(rs, stmt, null);
						}
					}
				}
		);
	}

	String save_ticket = "insert into jvf_ticket(cardsn,cardpwd,starttime,endtime,beat,pointb,addtime) values(?,?,?,?,?,?,now())";
	@SuppressWarnings("unchecked")
	public void saveTickets(final Map<String, String> ticketInfo,final int beat,
			final int pointb,final Date starttime,final Date endtime) {
		this.execute(save_ticket, new PreparedStatementCallback(){
			public Object doInPreparedStatement(PreparedStatement ps)
					throws SQLException, DataAccessException {
				Set<String> snSet = ticketInfo.keySet();
				for(String cardsn : snSet){
					ps.setString(1, cardsn);
					ps.setString(2, ticketInfo.get(cardsn));
					ps.setTimestamp(3, new Timestamp(starttime.getTime()));
					ps.setTimestamp(4, new Timestamp(endtime.getTime()));
					ps.setInt(5, beat);
					ps.setInt(6, pointb);
					ps.addBatch();
				}
				return ps.executeBatch();
			}
		});
	}

	final String findTicketById = "select cardsn,cardpwd,starttime,endtime,beat,pointb,state,addtime from jvf_ticket where id=?";
	public Ticket findTicketById(final int id) {
		return this.query(findTicketById, new ResultSetExtractor<Ticket>(){
			public Ticket extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				if(rs.next()){
					Ticket t = new Ticket();
					t.setId(id);
					t.setCardsn(rs.getString(1));
					t.setCardpwd(rs.getString(2));
					t.setStarttime(rs.getTimestamp(3));
					t.setEndtime(rs.getTimestamp(4));
					t.setBeat(rs.getInt(5));
					t.setPointb(rs.getInt(6));
					t.setState(rs.getInt(7));
					t.setAddtime(rs.getTimestamp(8));
					return t;
				}
				return null;
			}
		}, id);
	}

	final String updateTicket = "update jvf_ticket set cardsn=?,cardpwd=?,starttime=?,endtime=?,beat=?,pointb=?,state=? where id=?";
	public void updateTicket(Ticket ticket) {
		this.update(updateTicket,ticket.getCardsn(),ticket.getCardpwd(),ticket.getStarttime(),ticket.getEndtime(),
				ticket.getBeat(),ticket.getPointb(),ticket.getState(),ticket.getId());
	}
	
	
}
